/*
 * Copyright (c) 2017,安徽皖通科技股份有限公司 All rights reserved.
 */

package com.wttech.dzzf.process.dao;

import cn.com.taiji.sdk.model.comm.protocol.user.OBUUploadRequest;
import com.wtkj.dao.impl.ListResultSetExtractor;
import com.wtkj.vo.dzzf.base.kfwd.CardInfo;
import com.wtkj.vo.dzzf.base.kfwd.OBU;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * OBU信息挂起DAO
 *
 * @author MaShibo
 * @date 2017/12/28.
 */
@Repository
public class HangUpObuInfosDao {

	@Resource(name = "jdbcTemplate")
	private JdbcTemplate testJdbcTemplate;

	@Resource(name = "dzzfJdbcTemplate")
	private JdbcTemplate dzzfJdbcTemplate;

	public int queryObuTempNum() {
		final String sql = "SELECT COUNT(1) FROM BS_OBUTEMP ";
		return testJdbcTemplate.queryForObject(sql, Integer.class);
	}

	public List<String> queryObuIdsByRow(int startRow, int endRow) {
		final String sql =
				"SELECT OBUID\n" +
						"  FROM (SELECT A.OBUID, ROWNUM AS RM FROM BS_OBUTEMP A)\n" +
						" WHERE RM >= ?\n" +
						"   AND RM <= ?";
		return testJdbcTemplate.query(sql, (rs, rowNum) -> rs.getString("OBUID"), startRow, endRow);
	}

	public OBUUploadRequest queryObuByObuId(String obuId) {
		final String sql = "SELECT * FROM BS_OBU A WHERE A.ID = ? ";
		List<OBUUploadRequest> obus = testJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(OBUUploadRequest.class), obuId);
		return CollectionUtils.isEmpty(obus) ? null : obus.get(0);
	}

	@SuppressWarnings("unchecked")
	public OBU queryLastestObuById(String obuId) {
		final String sql =
				"SELECT *\n" +
						"  FROM (SELECT A.*,\n" +
						"               J.CHANNELID,\n" +
						"               G.BRAND,\n" +
						"               G.MODEL,\n" +
						"               ROW_NUMBER() OVER(PARTITION BY A.CONTRACTNO ORDER BY A.OPTON DESC) AS RM\n" +
						"          FROM AC_OBU A\n" +
						"          LEFT JOIN JG_ORGANIZATION J\n" +
						"            ON A.CLIENTNO = J.CODE\n" +
						"          LEFT JOIN KC_OBUGOODS G\n" +
						"            ON A.OBUNO = G.OBUNO\n" +
						"         WHERE A.CONTRACTNO = ?\n" +
						"           AND A.STATE = 99)\n" +
						" WHERE RM = 1";
		List<OBU> obus = (List<OBU>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new OBU()), obuId);
		return CollectionUtils.isEmpty(obus) ? null : obus.get(0);
	}

	public CardInfo queryCardInfoByCardInfoId(String cardInfoId) {
		final String sql = "SELECT * FROM AC_CARDINFO A WHERE A.CARDINFOID=? ";
		List<CardInfo> cardInfos = (List<CardInfo>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new CardInfo()), cardInfoId);
		return CollectionUtils.isEmpty(cardInfos) ? null : cardInfos.get(0);
	}

	public String queryCardInfoByVeh(String vehPlate, Integer vehColor) {
		final String sql =
				"SELECT USERID\n" +
						"  FROM (SELECT A.USERID,\n" +
						"               ROW_NUMBER() OVER(PARTITION BY A.VEHICLEPLATE, A.VEHICLECOLOR ORDER BY A.OPTON DESC) AS RM\n" +
						"          FROM AC_CARDINFO A\n" +
						"         WHERE A.VEHICLEPLATE = ?\n" +
						"           AND A.VEHICLECOLOR = ?\n" +
						"           AND A.USERID IS NOT NULL)\n" +
						" WHERE RM = 1";
		List<String> userIds = dzzfJdbcTemplate.query(sql, (rs, rowNum) -> rs.getString("USERID"), vehPlate, vehColor);
		return CollectionUtils.isEmpty(userIds) ? null : userIds.get(0);
	}

}
